from peewee import *
from browser.webui.components.dql_table_extractor import extract_table_names
# 数据库配置 - 与主应用配置保持一致
RDS_CONFIG = {
    'db_type': 'mysql',  # 或 'postgres'
    'host': '101.37.36.112',
    'port': 3306,
    'database': 'shujumulu',
    'user': 'zhaoolee',
    'password': 'accountZhaooleePassword',
    'charset': 'utf8mb4'
}

# 初始化数据库连接
db = MySQLDatabase(
    RDS_CONFIG['database'],
    host=RDS_CONFIG['host'],
    port=RDS_CONFIG['port'],
    user=RDS_CONFIG['user'],
    password=RDS_CONFIG['password'],
    charset=RDS_CONFIG['charset'],
)


# 基础模型类
class BaseModel(Model):
    class Meta:
        database = db


# 定义数据模型
class Sjml_Menu(BaseModel):
    class Meta:
        table_name = 'sjml_menu'
    id = AutoField(primary_key=True)
    menu_name = CharField(unique=False,null=True, max_length=255)
    menu_level = IntegerField(unique=False, null=True)
    parent_id = IntegerField(unique=False, null=True)
    system_name = CharField(unique=False,null=True, max_length=255)
    system_id = IntegerField(unique=False, null=True)

    def save(self, *args, **kwargs):
        return super().save(*args, **kwargs)

    

class Sjml_Menu_Sql(BaseModel):
    class Meta:
        table_name = 'sjml_menu_sql'
    id = AutoField(primary_key=True)
    menu_id = IntegerField(unique=False, null=True)
    sql_text = CharField(unique=False,null=True, max_length=255)
    system_name = CharField(unique=False,null=True, max_length=255)
    system_id = IntegerField(unique=False, null=True)

    def save(self, *args, **kwargs):
        return super().save(*args, **kwargs)

class Sjml_Menu_Table(BaseModel):
    class Meta:
        table_name = 'sjml_menu_table'
    id = AutoField(primary_key=True)
    menu_id = IntegerField(unique=False, null=True)
    table_name = CharField(unique=False,null=True, max_length=255)
    system_name = CharField(unique=False,null=True, max_length=255)
    system_id = IntegerField(unique=False, null=True)

    def save(self, *args, **kwargs):
        return super().save(*args, **kwargs)

# class Article(BaseModel):
#     title = CharField(max_length=200)
#     content = TextField()
#     author = ForeignKeyField(User, backref='articles')
#     published = BooleanField(default=False)
#     created_at = DateTimeField(default=datetime.datetime.now)

# 数据库操作类
class DatabaseOperations:
    def __init__(self):
        self.connect()
        # 确保表存在
        self.create_tables()

    def connect(self):
        """连接数据库"""
        if not db.is_closed():
            db.close()
        db.connect()

    def close(self):
        """关闭数据库连接"""
        if not db.is_closed():
            db.close()

    def create_tables(self):
        """创建数据表"""
        db.create_tables([Sjml_Menu, Sjml_Menu_Sql, Sjml_Menu_Table])

    # 用户相关操作
    def create_menu(self, menu_name, menu_level, parent_id, system_name, system_id):
        """创建新用户"""
        try:
            menu = Sjml_Menu.create(
                menu_name=menu_name,
                menu_level=menu_level,
                parent_id=parent_id,
                system_name=system_name,
                system_id=system_id,
            )
            print(f"创建菜单成功: {menu.id}")
            return menu
        except IntegrityError:
            return None

    def create_menu_sql(self, menu_id, sql_text, system_name, system_id):
        try:
            menu_sql = Sjml_Menu_Sql.create(
                menu_id=menu_id,
                sql_text=sql_text,
                system_name=system_name,
                system_id=system_id,
            )
            print(f"创建菜单sql成功: {menu_sql.id}")
            return menu_sql
        except IntegrityError:
            return None
    def create_menu_table(self, menu_id, table_name, system_name, system_id):
        try:
            menu_sql = Sjml_Menu_Table.create(
                menu_id=menu_id,
                table_name=table_name,
                system_name=system_name,
                system_id=system_id,
            )
            print(f"创建table成功: {menu_sql.id}")
            return menu_sql
        except IntegrityError:
            return None


db_ops = DatabaseOperations()


def process_directory(data, parent_path=None, result=None, parent_id=None):
    """
    处理目录层级结构并获取节点数据

    参数:
        data: 要处理的目录数据
        parent_path: 父节点路径，用于记录层级
        result: 存储结果的列表

    返回:
        包含所有节点信息的列表，每个元素包含路径和节点数据
    """
    # 初始化结果列表
    if result is None:
        result = []

    # 初始化父路径
    if parent_path is None:
        parent_path = []

    # 遍历当前层级的所有节点
    for node_name, node_data in data.items():
        # 当前节点的完整路径
        # current_path = parent_path + [node_name]

        # # 添加当前节点信息到结果
        # result_data = {
        #     'path': current_path,
        #     'level': len(current_path),  # 层级深度
        #     'node_name': node_name,
        #     'node_data': node_data
        # }
        # result.append(result_data)
        # menu = db_ops.create_menu(node_name, result_data['level'], parent_id, "数据采集", 1)
        # # 如果是字典类型，说明有子节点，递归处理
        # if isinstance(node_data, dict):
        #     parent_id = menu.id
        #     process_directory(node_data, current_path, result, parent_id)
        table_list = []
        table_i = []
        if isinstance(node_data, list):
            for node in node_data:
                tables = extract_table_names(node)
                if len(tables) > 0:
                    table_list.extend(tables)
                    db_ops.create_menu_sql(menu.id, node, "数据采集", 1)
            for table in table_list:
                if table not in table_i:
                    db_ops.create_menu_table(menu.id, table, "数据采集", 1)
                    table_i.append(table)

    return result

# 演示使用
if __name__ == '__main__':
    try:
        data = {'数据目录管理':
                    {'有效表识别管理': ['SELECT id, name FROM users;','select 1'],
                     '数据目录管理': ['SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_id;'],
                     '数据源管理': ['SELECT * FROM db1.public.products WHERE price > 100'],
                     '目录关联实体': ["SELECT * FROM (SELECT * FROM orders WHERE status = 'paid') t WHERE amount > 500;",
                                      "SELECT * FROM user_view uv JOIN departments d ON uv.dept_id = d.id;"]}}
        # 创建测试用户
        print("111111111111")
        processed_result = process_directory(data)
        # for item in processed_result:
        #     level = item['level']
        #     node_name = item['node_name']
        #     user = db_ops.create_menu(node_name, level, "数据采集", 1)
        #     if isinstance(item['node_data'], list):
        #         print(f"节点名称: {item['node_name']} 为叶子结点，数据: {item['node_data']}")
        #
        #     print(f"层级: {item['level']}")
        #     print(f"路径: {' -> '.join(item['path'])}")
        #     print(f"节点名称: {item['node_name']}")
        #     print(f"节点数据: {item['node_data']}")
        #     print("---")
        # user = db_ops.create_menu("1", 1, "1", 1)

        # if user:
        #     # 创建文章
        #     db_ops.create_article(
        #         title="Peewee数据库操作指南",
        #         content="这是一篇关于使用peewee操作数据库的示例文章...",
        #         author_id=user.id,
        #         published=True
        #     )
        #
        #     # 获取用户信息
        #     retrieved_user = db_ops.get_user(username="test_user")
        #     if retrieved_user:
        #         print(f"\n获取用户信息: {retrieved_user.username}, {retrieved_user.email}")
        #
        #         # 更新用户年龄
        #         db_ops.update_user(retrieved_user.id, age=31)
        #
        #         # 列出用户
        #         print("\n用户列表:")
        #         for u in db_ops.list_users():
        #             print(f"- {u.username} ({u.email}), 年龄: {u.age}")
        #
        #         # 获取用户文章
        #         print("\n用户文章:")
        #         for article in db_ops.get_user_articles(retrieved_user.id):
        #             print(f"- {article.title} (发布状态: {article.published})")

        # 清理测试数据（实际应用中谨慎使用）
        # if user:
        #     db_ops.delete_user(user.id)

    finally:
        db_ops.close()
